<?php
	require_once($_SERVER['DOCUMENT_ROOT'].'uno-course-scheduler/include/config.inc.php');
	
	if (isset($_GET['building_id']))
	{
		$building_id = mysql_real_escape_string(strip_tags($_GET['building_id']));
	}
	else
	{
		$getBuilding = TRUE;
	}
	
	if (isset($_GET['semester_id']))
	{
		$semester_id = mysql_real_escape_string(strip_tags($_GET['semester_id']));
	}
	else
	{
		$getSemester = TRUE;
	}
	
	if (isset($_GET['order']))
	{
		$order = mysql_real_escape_string(strip_tags($_GET['order']));
	}
	require_once($_SERVER['DOCUMENT_ROOT'].'uno-course-scheduler/include/top.inc.php');

	if (isset($getSemester))
	{?>
    	<h1>View Schedule</h1>
    	<p><strong>Select the semester you wish to view:</strong>
        <ul>
    	<?php
		$query = "SELECT *, sem.id AS sem_id
				  FROM semesters sem
				  JOIN seasons ssn ON ssn.id = sem.season_id
				  ORDER BY year DESC, season_id DESC";
		$result = mysql_query($query);
		while($row = mysql_fetch_assoc($result))
		{?>
        	<li><a href="postSchedule.php?semester_id=<?php echo $row['sem_id']; ?>"><?php echo $row['season'].' '.$row['year']; ?></a></li>
        <?php
		}
		?>
        </ul>
    <?php
	}
	else if (isset($getBuilding))
	{
		$query = "SELECT *, sem.id AS sem_id
				  FROM semesters sem
				  JOIN seasons ssn ON ssn.id = sem.season_id
				  WHERE sem.id = '$semester_id'";
		$result = mysql_query($query);
		$row = mysql_fetch_assoc($result);
		?>
        <p><a href="postSchedule.php">&lt;&lt; Back to semesters</a></p>
        <h1>View Schedule</h1>
    	<p><strong>Select the building schedule you wish to view for <?php echo $row['season'].' '.$row['year']; ?>:</strong>
        <ul>
    	<?php
		$query = "SELECT *
				  FROM buildings 
				  ORDER BY building_name";
		$result2 = mysql_query($query);
		while($row2 = mysql_fetch_assoc($result2))
		{?>
        	<li><a href="postSchedule.php?semester_id=<?php echo $row['sem_id']; ?>&amp;building_id=<?php echo $row2['id']; ?>"><?php echo $row2['building_name']; ?></a></li>
        <?php
		}
		?>
        </ul>
    <?php	
	}
	else
	{
		$query1 = "SELECT *, sem.id AS sem_id
				  FROM semesters sem
				  JOIN seasons ssn ON ssn.id = sem.season_id
				  WHERE sem.id = '$semester_id'";
		$result1 = mysql_query($query1);
		$row1 = mysql_fetch_assoc($result1);
		$query2 = "SELECT *
				  FROM buildings 
				  WHERE id = '$building_id'";
		$result2 = mysql_query($query2);
		$row2 = mysql_fetch_assoc($result2);
		?>
		<p><a href="postSchedule.php?semester_id=<?php echo $semester_id; ?>">&lt;&lt; Back to buildings</a></p>
        <?php
		
		$query = 
				   "SELECT *, cs.id AS cs_id
					FROM course_sections cs
					JOIN instructors i ON i.id = cs.instructor_id
					JOIN course_section_mapping csm ON csm.course_section_id = cs.id
					JOIN courses c ON c.id = csm.course_id
					JOIN course_subjects csub ON csub.id = c.course_subject_id
			  		JOIN building_subject_mapping bsm ON bsm.course_subject_id = csub.id
			  		JOIN buildings b ON b.id = bsm.building_id
					WHERE cs.id NOT IN (SELECT course_section_id
										FROM schedule s)
					AND b.id = '$building_id'";
		if (isset($order))
        {
            switch ($order){
                case 'inst':
                    $query .= " ORDER BY last_name, first_name";
                    break;
                case 'course':
                default:
                    $query .= " ORDER BY csub.abbreviation, course_number";
                    break;
            }
        }
        else
        {
            $query .= " ORDER BY csub.abbreviation, course_number";
        }
					
		$result = mysql_query($query);
		//echo mysql_error();
		if (mysql_num_rows($result) > 0)
		{?>
        	<h1>Unscheduled Courses for <?php echo $row2['abbreviation'].' - '.$row1['season'].' '.$row1['year']; ?></h1>
			
            
            
            <table class="postSchedule">
                <tr>
                    <th colspan="2"></th>
                    <th><a href="postSchedule.php?order=course&amp;building_id=<?php echo $building_id; ?>&amp;semester_id=<?php echo $semester_id; ?>">Course/Section</a></th>
                    <th><a href="postSchedule.php?order=inst&amp;building_id=<?php echo $building_id; ?>&amp;semester_id=<?php echo $semester_id; ?>">Instructor</a></th>
                </tr>
            	<?php
            	$row_count = 0;
                while ($row = mysql_fetch_assoc($result)) {
					$row_count++;
                    $instructor = $row['last_name'];
                    $course_number = $row['course_number'];
                    $abbr = $row['abbreviation'];
                    $sem_id = $row['semester_id'];
                    ?>
                    
                    <tr<?php if (($row_count%2) != 0) echo " class=\"zebra_stripe\""; ?>>
                        <td><a href="addSchedule.php?cs_id=<?php echo $row['cs_id']; ?>&amp;building_id=<?php echo $building_id; ?>&amp;semester_id=<?php echo $semester_id; ?>">Schedule Course</a></td>
                        <td><a href="collision_log.php?id=<?php echo $row['cs_id']; ?>&amp;building_id=<?php echo $building_id; ?>&amp;semester_id=<?php echo $semester_id; ?>">Collision Log</a></td>
                    <td> 
                    <?php
					$course_section_id = $row['course_section_id'];
					
					$q2 = "SELECT *
						   FROM course_sections cs
						   JOIN course_section_mapping csm ON csm.course_section_id = cs.id
						   JOIN courses c on c.id = csm.course_id
						   JOIN course_subjects csub ON csub.id = c.course_subject_id
						   WHERE cs.id = '$course_section_id'
						   ORDER BY c.course_number ASC";
					$r2 = mysql_query($q2);
					$num_courses = mysql_num_rows($r2);
					$count = 0;
					while($row2 = mysql_fetch_assoc($r2))
					{
						$count++;
						echo $row2['abbreviation'].' '.$row2['course_number'].' - ';
						if (strlen($row2['section_number']) == 1)
							{echo "00".$row2['section_number'];}
						else if (strlen($row2['section_number']) == 2)
							{echo "0".$row2['section_number'];}
						else
							{echo $row['section_number'];}
						if ($row2['honors_program'] == 1  && $row2['course_number'] < 5000)
						{
							echo "<br/>".$row2['abbreviation'].' '.$row2['course_number'].' - 099';	
						}
						
						if ($count != $num_courses)
						{
							echo "<br />";
						}
					}
					?>
                    </td>
                    <td><?php echo $instructor; ?></td>
                    
                    </tr>
                
                <?php
                } ?>
            </table>       
    	<?php
		} 
		else
		{?>
        	<p><strong>There are no unscheduled courses at this time.</strong></p>
        <?php
		}
		
		$query1 = "SELECT *, sem.id AS sem_id
				  FROM semesters sem
				  JOIN seasons ssn ON ssn.id = sem.season_id
				  WHERE sem.id = '$semester_id'";
		$result1 = mysql_query($query1);
		$row1 = mysql_fetch_assoc($result1);
		$query2 = "SELECT *
				  FROM buildings 
				  WHERE id = '$building_id'";
		$result2 = mysql_query($query2);
		$row2 = mysql_fetch_assoc($result2);
		
		$csv_file = 'files/'.$row2['abbreviation'].'_'.$row1['season'].'_'.$row1['year'].'_schedule.csv';
		?>
        
        <h1>Scheduled Courses for <?php echo $row2['abbreviation'].' - '.$row1['season'].' '.$row1['year']; ?></h1>
        
    	
        <?php
        $query = "SELECT *, b.abbreviation AS b_abbrev, csub.abbreviation AS sub_abbrev, cs.id AS course_section_id, s.id AS schedule_id, cs.num_seats AS cs_num_seats
                  FROM schedule s
                  JOIN course_sections cs ON cs.id = s.course_section_id
                  JOIN course_section_mapping csm ON csm.course_section_id = cs.id
                  JOIN courses c on c.id = csm.course_id
                  JOIN course_subjects csub ON csub.id = c.course_subject_id
                  JOIN instructors i ON i.id = cs.instructor_id
                  JOIN classrooms cr ON cr.id = s.classroom_id
                  JOIN buildings b ON b.id = cr.building_id
                  JOIN time_slots ts ON ts.id = s.time_slot_id
                  WHERE semester_id = '$semester_id'
                  AND cr.building_id = '$building_id'
                  GROUP BY cs.id ";
        if (isset($order))
        {
            switch ($order){
                case 'inst':
                    $query .= " ORDER BY last_name, first_name, Monday DESC, Tuesday DESC, Wednesday DESC, Thursday DESC, Friday DESC, Saturday DESC, Sunday DESC, start_time ASC, end_time ASC";
                    break;
                case 'room':
                    $query .= " ORDER BY b_abbrev, room_num, Monday DESC, Tuesday DESC, Wednesday DESC, Thursday DESC, Friday DESC, Saturday DESC, Sunday DESC, start_time ASC, end_time ASC";
                    break;
                case 'day':
                    $query .= " ORDER BY Monday DESC, Tuesday DESC, Wednesday DESC, Thursday DESC, Friday DESC, Saturday DESC, Sunday DESC, start_time ASC, end_time ASC, b_abbrev ASC, room_num ASC";
                    break;
                case 'time':
                    $query .= " ORDER BY start_time, end_time, Monday DESC, Tuesday DESC, Wednesday DESC, Thursday DESC, Friday DESC, Saturday DESC, Sunday DESC, b_abbrev, room_num";
                    break;
                
                case 'course':
                default:
                    $query .= " ORDER BY sub_abbrev, course_number";
                    break;
            }
        }
        else
        {
            $query .= " ORDER BY sub_abbrev, course_number";
        }
        $result = mysql_query($query);
		if (mysql_num_rows($result) == 0)
		{ ?>
			<p><strong>There are no courses scheduled at this time.</strong></p>
        <?php
		}
		else
		{ ?>
        	<p class="export_button"><a href="<?php echo $csv_file; ?>" class="export_button">Export Schedule to Spreadsheet</a></p>
			<table class="postSchedule">
                <tr>
                    <th colspan="3"></th>
                    <th><a href="postSchedule.php?order=course&amp;building_id=<?php echo $building_id; ?>&amp;semester_id=<?php echo $semester_id; ?>">Course/Section</a></th>
                    <th><a href="postSchedule.php?order=inst&amp;building_id=<?php echo $building_id; ?>&amp;semester_id=<?php echo $semester_id; ?>">Instructor</a></th>
                    <th><a href="postSchedule.php?order=room&amp;building_id=<?php echo $building_id; ?>&amp;semester_id=<?php echo $semester_id; ?>">Room</a></th>
                    <th><a href="postSchedule.php?order=day&amp;building_id=<?php echo $building_id; ?>&amp;semester_id=<?php echo $semester_id; ?>">Days</a></th>
                    <th><a href="postSchedule.php?order=time&amp;building_id=<?php echo $building_id; ?>&amp;semester_id=<?php echo $semester_id; ?>">Time</a></th>
                </tr>
            <?php
			$row_count = 0;
			$csv = "Course ID,Course Title,Credit Hours,Class Days,Start time,End Time,Bldg,Room,Mas Seats,Instructor,Comment\n";
			while($row = mysql_fetch_assoc($result))
			{
				$row_count++;
				?>
				<tr<?php if (($row_count%2) != 0) echo " class=\"zebra_stripe\""; ?>>
					<td><a href="editSchedule.php?building_id=<?php echo $building_id; ?>&amp;schedule_id=<?php echo $row['schedule_id']; ?>&amp;semester_id=<?php echo $semester_id; ?>">Edit</a></td>
					<td><a href="delete_course.php?building_id=<?php echo $building_id; ?>&amp;schedule_id=<?php echo $row['schedule_id']; ?>&amp;semester_id=<?php echo $semester_id; ?>">Delete</a></td>
					<td><a href="collision_log.php?id=<?php echo $row['course_section_id']; ?>&amp;building_id=<?php echo $building_id; ?>&amp;semester_id=<?php echo $semester_id; ?>">Collision Log</a></td>
					<td>
				
					<?php
					$course_section_id = $row['course_section_id'];
					
					$q2 = "SELECT *
						   FROM course_sections cs
						   JOIN course_section_mapping csm ON csm.course_section_id = cs.id
						   JOIN courses c on c.id = csm.course_id
						   JOIN course_subjects csub ON csub.id = c.course_subject_id
						   WHERE cs.id = '$course_section_id'
						   ORDER BY c.course_number ASC";
					$r2 = mysql_query($q2);
					$num_courses = mysql_num_rows($r2);
					$count = 0;
					while($row2 = mysql_fetch_assoc($r2))
					{
						$count++;
						echo $row2['abbreviation'].' '.$row2['course_number'].' - ';
						$csv .= $row2['abbreviation'].'-'.$row2['course_number'].'-';
						if (strlen($row2['section_number']) == 1)
						{
							echo "00".$row2['section_number'];
							$csv .= '00'.$row2['section_number'].',';
						}
						else if (strlen($row2['section_number']) == 2)
						{
							echo "0".$row2['section_number'];
							$csv .= '0'.$row2['section_number'].',';
						}
						else
						{
							echo $row['section_number'];
							$csv .= $row2['section_number'].',';
						}
						
						$csv .= $row2['title'].',';
						$csv .= $row['hours_per_week'].',';
						
						$days_of_week = '';
						if ($row['Monday'])
							$days_of_week .= 'M ';
						if ($row['Tuesday'])
							$days_of_week .= 'Tu ';
						if ($row['Wednesday'])
							$days_of_week .= 'W ';
						if ($row['Thursday'])
							$days_of_week .= 'Th ';
						if ($row['Friday'])
							$days_of_week .= 'F ';
						if ($row['Saturday'])
							$days_of_week .= 'Sa ';
						if ($row['Sunday'])
							$days_of_week .= 'Su ';
						$days_of_week = substr_replace($days_of_week, '', -1, 1);
						$csv .= $days_of_week.',';
						
						$csv .= date('hiA', strtotime($row['start_time'])).',';
						$csv .= date('hiA', strtotime($row['end_time'])).',';
						
						$csv .= $row['b_abbrev'].',';
						$csv .= $row['room_num'].',';
						$csv .= $row['cs_num_seats'].',';
						if ($row['first_name'] != 'STAFF' && $row['first_name'] != 'TBA')
						{
							$csv .= $row['last_name'].' '.$row['first_name'].',';
						}
						else
						{
							$csv .= $row['first_name'].',';
						}
						$csv .= "\n";
						
						if ($row2['honors_program'] == 1  && $row2['course_number'] < 5000)
						{
							echo "<br/>".$row2['abbreviation'].' '.$row2['course_number'].' - 099';	
							
							$csv .= $row2['abbreviation'].'-'.$row2['course_number'].'-099,';
							$csv .= $row2['title'].',';
							$csv .= $row['hours_per_week'].',';
							
							$days_of_week = '';
							if ($row['Monday'])
								$days_of_week .= 'M ';
							if ($row['Tuesday'])
								$days_of_week .= 'Tu ';
							if ($row['Wednesday'])
								$days_of_week .= 'W ';
							if ($row['Thursday'])
								$days_of_week .= 'Th ';
							if ($row['Friday'])
								$days_of_week .= 'F ';
							if ($row['Saturday'])
								$days_of_week .= 'Sa ';
							if ($row['Sunday'])
								$days_of_week .= 'Su ';
							$days_of_week = substr_replace($days_of_week, '', -1, 1);
							$csv .= $days_of_week.',';
							
							$csv .= date('hiA', strtotime($row['start_time'])).',';
							$csv .= date('hiA', strtotime($row['end_time'])).',';
							
							$csv .= $row['b_abbrev'].',';
							$csv .= $row['room_num'].',';
							$csv .= $row['cs_num_seats'].',';
							if ($row['first_name'] != 'STAFF' && $row['first_name'] != 'TBA')
							{
								$csv .= $row['last_name'].' '.$row['first_name'].',';
							}
							else
							{
								$csv .= $row['first_name'].',';
							}
							$csv .= "\n";
						}
						
						if ($count != $num_courses)
						{
							echo "<br />";
						}
					}
					?>
					</td>
					<td><?php 
						if ($row['first_name'] != 'STAFF' && $row['first_name'] != 'TBA')
						{
							echo htmlspecialchars($row['last_name']).', '.htmlspecialchars($row['first_name']);
						}
						else
						{
							echo $row['first_name'];
						}
					?></td>
					<td><?php echo $row['b_abbrev'].' '.$row['room_num']; ?></td>
					<td><?php
						$days_of_week = '';
						if ($row['Monday'])
							$days_of_week .= 'M,';
						if ($row['Tuesday'])
							$days_of_week .= 'Tu,';
						if ($row['Wednesday'])
							$days_of_week .= 'W,';
						if ($row['Thursday'])
							$days_of_week .= 'Th,';
						if ($row['Friday'])
							$days_of_week .= 'F,';
						if ($row['Saturday'])
							$days_of_week .= 'Sa,';
						if ($row['Sunday'])
							$days_of_week .= 'Su,';
						$days_of_week = substr_replace($days_of_week, '', -1, 1);
						echo $days_of_week;
							
					
					?></td>
					<td><?php 
						echo date('g:i', strtotime($row['start_time'])); 
						if (date('a', strtotime($row['start_time'])) != date('a', strtotime($row['end_time'])))
						{
							echo date('a', strtotime($row['start_time']));
						}
						echo ' - '.date('g:ia', strtotime($row['end_time']));
					?></td>
					
				</tr>
				
			<?php
			}
			
			$fh = fopen($csv_file, 'w');
			fwrite($fh, $csv);
			fclose($fh);
			
			?>
		
			</table>
		<?php
		}
	}
	
	require_once($_SERVER['DOCUMENT_ROOT'].'uno-course-scheduler/include/bottom.inc.php');
?>